package com.study.jdbc.dao.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.study.jdbc.dao.CustomerDao;
import com.study.jdbc.domain.Customer;
import com.study.jdbc.utils.DBUtil;

/**
* @Name: CustomerDaoJDBCImpl
* @Description: 用户CRUD操作dao接口JDBC实现类：使用自定义JDBC工具类（不使用数据源）
* @Author: 李超	lc_jack@foxmail.com
* @CreateDate: 2017-2-16 下午7:43:46 
* @Version: V1.0
 */
public class CustomerDaoJDBCImpl implements CustomerDao {
	
	@Override
	public Customer findCustomerById(Serializable id) {
		Connection con = null ;
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		Customer customer = null ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description " +
				"from t_customer " +
				"where id = ? ; " ;
			//创建sql语句执行对象
			ps = con.prepareStatement(sql) ;
			//预处理sql语句
			ps.setString(1, (String) id) ;
			//执行sql语句，获取查询结果集对象
			rs = ps.executeQuery() ;
			//遍历，封装查询结果
			if(rs.next()) {
				customer = new Customer() ;
				customer.setId(rs.getString("id")) ;
				customer.setName(rs.getString("name")) ;
				customer.setGender(rs.getString("gender")) ;
				customer.setBirthday(rs.getDate("birthday")) ;
				customer.setCellphone(rs.getString("cellphone")) ;
				customer.setEmail(rs.getString("email")) ;
				customer.setPreference(rs.getString("preference")) ;
				customer.setType(rs.getString("type")) ;
				customer.setDescription(rs.getString("description")) ;
			}
		} catch (Exception e) {
			System.out.println("查询失败！");
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, ps, rs) ;
		}
		return customer ;
	}

	@Override
	public List<Customer> findAllCustomers() {
		Connection con = null ;
		Statement s = null ;
		ResultSet rs = null ;
		List<Customer> customers = new ArrayList<Customer>() ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			//创建sql语句执行对象
			s = con.createStatement() ;
			String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description from t_customer ; " ;
			//执行sql语句，获取查询结果集对象
			rs = s.executeQuery(sql) ;
			//遍历、封装查询结果
			while(rs.next()) {
				Customer customer = new Customer() ;
				customer.setId(rs.getString("id")) ;
				customer.setName(rs.getString("name")) ;
				customer.setGender(rs.getString("gender")) ;
				customer.setBirthday(rs.getDate("birthday")) ;
				customer.setCellphone(rs.getString("cellphone")) ;
				customer.setEmail(rs.getString("email")) ;
				customer.setPreference(rs.getString("preference")) ;
				customer.setType(rs.getString("type")) ;
				customer.setDescription(rs.getString("description")) ;
				customers.add(customer) ;
			}
		} catch (Exception e) {
			System.out.println("查询失败！");
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, s, rs) ;
		}
 		return customers ;
	}

	@Override
	public void saveCustomer(Customer customer) {
		Connection con = null ;
		PreparedStatement ps = null ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			String sql = "insert into " +
					" t_customer(id, name, gender, birthday, cellphone, email, preference, type, description) " +
					" values(?, ?, ?, ?, ?, ?, ?, ?, ?) ; " ;
			//创建sql语句执行对象
			ps = con.prepareStatement(sql) ;
			//预处理
			ps.setString(1, customer.getId()) ;
			ps.setString(2, customer.getName()) ;
			ps.setString(3, customer.getGender()) ;
			ps.setDate(4, new Date(customer.getBirthday().getTime())) ;
			ps.setString(5, customer.getCellphone()) ;
			ps.setString(6, customer.getEmail()) ;
			ps.setString(7, customer.getPreference()) ;
			ps.setString(8, customer.getType()) ;
			ps.setString(9, customer.getDescription()) ;
			//执行sql语句
			int result = ps.executeUpdate() ;
			if(result > 0) {
				System.out.println("插入成功！");
			}
		} catch (Exception e) {
			System.out.println("插入失败！");
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, ps, null) ;
		}
	}

	@Override
	public void updateCustomer(Customer customer) {
		Connection con = null ;
		PreparedStatement ps = null ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			String sql = "update t_customer set " +
					" name = ?, " +
					" gender = ?, " +
					" birthday = ?, " +
					" cellphone = ?, " +
					" email = ?, " +
					" preference = ?, " +
					" type = ?, " +
					" description = ? " +
					" where id = ? ; " ;
			//创建sql语句执行对象
			ps = con.prepareStatement(sql) ;
			//预处理
			ps.setString(1, customer.getName()) ;
			ps.setString(2, customer.getGender()) ;
			ps.setDate(3, new Date(customer.getBirthday().getTime())) ;
			ps.setString(4, customer.getCellphone()) ;
			ps.setString(5, customer.getEmail()) ;
			ps.setString(6, customer.getPreference()) ;
			ps.setString(7, customer.getType()) ;
			ps.setString(8, customer.getDescription()) ;
			ps.setString(9, customer.getId()) ;
			//执行sql语句
			int result = ps.executeUpdate() ;
			if(result > 0) {
				System.out.println("更新成功！");
			}
		} catch (Exception e) {
			System.out.println("更新失败！");
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, ps, null) ;
		}
	}

	@Override
	public void deleteCustomerById(Serializable id) {
		Connection con = null ;
		PreparedStatement ps = null ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			String sql = "delete from t_customer where id = ? ; " ;
			//创建sql语句执行对象
			ps = con.prepareStatement(sql) ;
			//预处理
			ps.setString(1, (String) id) ;
			//执行sql语句
			int result = ps.executeUpdate() ;
			if(result > 0) {
				System.out.println("删除成功！");
			}
		} catch (Exception e) {
			System.out.println("删除失败！");
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, ps, null) ;
		}
	}

	@Override
	public int getTotalRecordsCount() {
		return findAllCustomers().size() ;
	}

	@Override
	public List<Customer> findPageRecords(int startIndex, int pageSize) {
		Connection con = null ;
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		List<Customer> pageList = new ArrayList<Customer>() ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description" +
					" from t_customer" +
					" limit ?, ? ; " ;
			//创建sql语句执行对象
			ps = con.prepareStatement(sql) ;
			//预处理
			ps.setInt(1, startIndex) ;
			ps.setInt(2, pageSize) ;
			//执行sql语句，获取分页记录结果集对象
			rs = ps.executeQuery() ;
			//遍历结果集
			while(rs.next()) {
				Customer customer = new Customer() ;
				customer.setId(rs.getString("id")) ;
				customer.setName(rs.getString("name")) ;
				customer.setGender(rs.getString("gender")) ;
				customer.setBirthday(rs.getDate("birthday")) ;
				customer.setCellphone(rs.getString("cellphone")) ;
				customer.setEmail(rs.getString("email")) ;
				customer.setPreference(rs.getString("preference")) ;
				customer.setType(rs.getString("type")) ;
				customer.setDescription(rs.getString("description")) ;
				pageList.add(customer) ;
			}
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, ps, rs) ;
		}
		return pageList;
	}

}


